﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web.Script.Serialization;
using JYH.Data;
using JYH.Framework;


namespace JYH.DataAccess
{
   public class BusinessTool
    {

        /// <summary>
        /// 获取实体类转Insert语句
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string GetInsertSql(Object model)
        {
            string res = string.Empty;
            if (model == null) return "";
            string _ColumnName = "";
            string _ColumnValue = "";
            Type type = model.GetType();//assembly.GetType("Reflect_test.PurchaseOrderHeadManageModel", true, true); //命名空间名称 + 类名  

            Object[] TableNames = type.GetCustomAttributes(typeof(TableNameAttribute), true);

            //创建类的实例  
            //object obj = Activator.CreateInstance(type, true)

            //获取公共属性  
            PropertyInfo[] Propertys = type.GetProperties();
            for (int i = 0; i < Propertys.Length; i++)
            {
                // Propertys[i].SetValue(Propertys[i], i, null); //设置值  
                PropertyInfo pi = type.GetProperty(Propertys[i].Name);
                object val = pi.GetValue(model, null);
                string value = val == null ? "" : val.ToString(); ;
                Object[] columns = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
                if (columns.Length > 0)
                {
                    if (string.Equals(columns[0].GetType().Name, "ColumnAttribute"))
                    {
                        _ColumnName += Propertys[i].Name + ",";
                        if (!string.IsNullOrEmpty(value))
                        {

                            
                            _ColumnValue += "'" + StringHelper.ConvertFullWidthToHalfWidth(StringHelper.ReplaceSQLChar(value))+ "',";
                        }
                        else
                        {

                            _ColumnValue += "NULL,";
                        }
                    }


                   
                    
                   
                }


                // Console.WriteLine("属性名：{0},类型：{1}", Propertys[i].Name, Propertys[i].PropertyType);
            }
            _ColumnName = _ColumnName.Substring(0, _ColumnName.Length - 1);
            _ColumnValue = _ColumnValue.Substring(0, _ColumnValue.Length - 1);

            res = "insert into " + ((TableNameAttribute)TableNames[0]).Value + "(" + _ColumnName + ")  values (" + _ColumnValue + ")";
            ///StringBuilder sb = new StringBuilder();
            //sb.Append(@" begin
            //         " + res + @";
            //         dbms_output.put_line(to_char(sql%rowcount));
            //         commit;
            //       end;");

            return res;
        }
        /// <summary>
        /// 根据Model生成修改SQL语句
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string GetUpdateSql(Object model)
        {
            string res = string.Empty;
            if (model == null) return "";
            Type type = model.GetType();

            Object[] TableNames = type.GetCustomAttributes(typeof(TableNameAttribute), true);
            Object[] PrimaryKeys = type.GetCustomAttributes(typeof(PrimaryKeyAttribute), true);

            string __PrimaryValue = "";
            string __Update = " SET ";

            PropertyInfo[] Propertys = type.GetProperties();

            for (int i = 0; i < Propertys.Length; i++)
            {
                // Propertys[i].SetValue(Propertys[i], i, null); //设置值  
                PropertyInfo pi = type.GetProperty(Propertys[i].Name);
                object val = pi.GetValue(model, null);
                string value = val == null ? "" : val.ToString();
                Object[] columns = Propertys[i].GetCustomAttributes(typeof(ColumnAttribute), true);
                if (columns.Length > 0)
                {
                    if (string.Equals(columns[0].GetType().Name, "ColumnAttribute"))
                    {
                        if (string.Equals(Propertys[i].Name, ((PrimaryKeyAttribute)PrimaryKeys[0]).Value))//如果是關鍵字
                        {
                            __PrimaryValue = value;
                            continue;
                        }
                        else
                        {

                            __Update += Propertys[i].Name + "=";

                            //__ColumnName += Propertys[i].Name + ",";

                            if (!string.IsNullOrEmpty(value))
                            {

                                __Update += "'" + StringHelper.ConvertFullWidthToHalfWidth(value) + "',";
                            }
                            else
                            {

                                __Update += "NULL,";
                            }

                        }

                    }
                }


                // Console.WriteLine("属性名：{0},类型：{1}", Propertys[i].Name, Propertys[i].PropertyType);
            }
            __Update = __Update.Substring(0, __Update.Length - 1);
            //res = "Delete  " + type.Name + " Where " + ((PrimaryKey)PrimaryKeys[0]).Value + "='" + sbval + "'";
            res = "UPDATE  " + ((TableNameAttribute)TableNames[0]).Value + __Update + " Where " + ((PrimaryKeyAttribute)PrimaryKeys[0]).Value + "='" + StringHelper.ReplaceSQLChar(__PrimaryValue) + "'";
            return res;

        }
        /// <summary>
        /// 根据Model生成删除SQL语句
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string GetDeleteSql(Object model)
        {
            string res = string.Empty;
            if (model == null) return "";
            Type type = model.GetType();

            Object[] TableNames = type.GetCustomAttributes(typeof(TableNameAttribute), true);
            Object[] PrimaryKeys = type.GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
            string sbval = "";
            PropertyInfo[] Propertys = type.GetProperties();
            for (int i = 0; i < Propertys.Length; i++)
            {
                // Propertys[i].SetValue(Propertys[i], i, null); //设置值  
                PropertyInfo pi = type.GetProperty(Propertys[i].Name);


                if (string.Equals(Propertys[i].Name, ((PrimaryKeyAttribute)PrimaryKeys[0]).Value))
                {
                    object val = pi.GetValue(model, null);
                    string value = val == null ? "" : val.ToString();
                    sbval = value;
                    break;
                }


                // Console.WriteLine("属性名：{0},类型：{1}", Propertys[i].Name, Propertys[i].PropertyType);
            }

            res = "Delete  " + ((TableNameAttribute)TableNames[0]).Value + " Where " + ((PrimaryKeyAttribute)PrimaryKeys[0]).Value + "='" + sbval + "'";
            return res;

        }

        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="model">查询对象</param>
        /// <returns></returns>
        public static string GetWhereSql(Object model)
        {
            string res = string.Empty;
            if (model == null) return "";
            Type type = model.GetType();

            Sql sql = new Sql();
            Type t = model.GetType();

            for (int i = 0; i < t.GetProperties().Length; i++)
            {
                Object[] columns = t.GetProperties()[i].GetCustomAttributes(typeof(ColumnAttribute), true);
                object obj = t.GetProperties()[i].GetValue(model, null);
                if (obj != null && columns.Length > 0 && string.Equals(columns[0].GetType().Name, "ColumnAttribute"))
                {
                    if(!string.IsNullOrEmpty(obj.ToString()))
                    sql.Append("Where " + t.Name + "." + t.GetProperties()[i].Name + "='" + obj.ToString() + "'");
                }
            }
            res = sql.SQL;
            return res;
        }

        /// <summary>
        /// 获取当前时间yyyy-MM-dd HH:mm:ss
        /// </summary>
        /// <returns></returns>
        public static string GetDateTime()
        {
            return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        }
        /// <summary>
        /// 获取当前时间yyyy-MM-dd
        /// </summary>
        /// <returns></returns>
        public static string GetDate()
        {
            return DateTime.Now.ToString("yyyy-MM-dd");
        }

        /// <summary>
        /// 去掉导入的文档里存在的空行
        /// </summary>
        /// <param name="dt"></param>
        public static void RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {

                        rowdataisnull = false;
                    }

                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }


        /// <summary>
        /// 全角转半角
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static string ToDBC(string input)
        {
            char[] array = input.ToCharArray();
            for (int i = 0; i < array.Length; i++)
            {
                if (array[i] == 12288)
                {
                    array[i] = (char)32;
                    continue;
                }
                if (array[i] > 65280 && array[i] < 65375)
                {
                    array[i] = (char)(array[i] - 65248);
                }
            }
            return new string(array);
        }
        /// <summary>
        /// Json特殊符号处理
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        /// 

        public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
            {
                propertyNameList.AddRange(propertyName);
            }
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        //if (DBNull.Value.Equals(pi.PropertyType))
                        //{
                        //   // pi.PropertyType = DateTime;
                        //}
                        Type colType = pi.PropertyType;
                        if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        result.Columns.Add(pi.Name, colType);
                        //result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                        {
                            result.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }

            }
            return result;
        }
        /// <summary>
        /// list转DateTable （新）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static DataTable NewToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
            {
                propertyNameList.AddRange(propertyName);
            }
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        //if (DBNull.Value.Equals(pi.PropertyType))
                        //{
                        //   // pi.PropertyType = DateTime;
                        //}
                        Type colType = pi.PropertyType;
                        if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        result.Columns.Add(pi.Name, colType);
                        //result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                        {
                            result.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
        /// <summary>
        /// EXcel初始化
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <returns></returns>
        public DataTable GetExcelDatatable(string fileUrl)
        {

            ExcelHelper excelHelper = new ExcelHelper();
            System.Data.DataTable dt = null;
            dt = excelHelper.GetExcelDataAsTable(fileUrl);
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            string cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileUrl + ";  Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

            return dt;
        }
        /// <summary>
        /// 时间戳反转为时间，有很多中翻转方法，但是，请不要使用过字符串（string）进行操作，大家都知道字符串会很慢！
        /// </summary>
        /// <param name="TimeStamp">时间戳</param>
        /// <param name="AccurateToMilliseconds">是否精确到毫秒</param>
        /// <returns>返回一个日期时间</returns>
        public static DateTime GetTime(long TimeStamp, bool AccurateToMilliseconds = false)
        {
            System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
            if (AccurateToMilliseconds)
            {
                return startTime.AddTicks(TimeStamp * 10000);
            }
            else
            {
                return startTime.AddTicks(TimeStamp * 10000000);
            }
        }
        /// <summary>
        /// 取时间戳，高并发情况下会有重复。想要解决这问题请使用sleep线程睡眠1毫秒。
        /// </summary>
        /// <param name="AccurateToMilliseconds">精确到毫秒</param>
        /// <returns>返回一个长整数时间戳</returns>
        public static long GetTimeStamp(bool AccurateToMilliseconds = false)
        {
            if (AccurateToMilliseconds)
            {

                // 使用当前时间计时周期数（636662920472315179）减去1970年01月01日计时周期数（621355968000000000）除去（删掉）后面4位计数（后四位计时单位小于毫秒，快到不要不要）再取整（去小数点）。

                //备注：DateTime.Now.ToUniversalTime不能缩写成DateTime.Now.Ticks，会有好几个小时的误差。

                //621355968000000000计算方法 long ticks = (new DateTime(1970, 1, 1, 8, 0, 0)).ToUniversalTime().Ticks;

                return (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000;

            }
            else
            {

                //上面是精确到毫秒，需要在最后除去（10000），这里只精确到秒，只要在10000后面加三个0即可（1秒等于1000毫米）。
                return (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
            }
        }


        /// <summary>
        /// DataTable写入CSV文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fullPath"></param>
        public static void SaveCSV(DataTable dt, string fullPath)//table数据写入csv
        {
            FileInfo fi = new FileInfo(fullPath);
            if (!fi.Directory.Exists)
            {
                fi.Directory.Create();
            }
            FileStream fs = new FileStream(fullPath, FileMode.Create,
              FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
            string data = "";

            for (int i = 0; i < dt.Columns.Count; i++)//写入列名
            {
                data += dt.Columns[i].ColumnName.ToString();
                if (i < dt.Columns.Count - 1)
                {
                    data += ",";
                }
            }
            sw.WriteLine(data);

            for (int i = 0; i < dt.Rows.Count; i++) //写入各行数据
            {
                data = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string str = dt.Rows[i][j].ToString();
                    str = str.Replace("\"", "\"\"");//替换英文冒号 英文冒号需要换成两个冒号
                    if (str.Contains(',') || str.Contains('"')
                      || str.Contains('\r') || str.Contains('\n')) //含逗号 冒号 换行符的需要放到引号中
                    {
                        str = string.Format("\"{0}\"", str);
                    }

                    data += str;
                    if (j < dt.Columns.Count - 1)
                    {
                        data += ",";
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
        }

        public static DataTable OpenCSV(string filePath)//从csv读取数据返回table
        {
            Encoding encoding = GetType(filePath); //Encoding.ASCII;//
            DataTable dt = new DataTable();
            FileStream fs = new FileStream(filePath, FileMode.Open,
              FileAccess.Read);

            StreamReader sr = new StreamReader(fs, encoding);
            //记录每次读取的一行记录
            string strLine = "";
            //记录每行记录中的各字段内容
            string[] aryLine = null;
            string[] tableHead = null;
            //标示列数
            int columnCount = 0;
            //标示是否是读取的第一行
            //  bool IsFirst = true;
            while ((strLine = sr.ReadLine()) != null)
            {


                tableHead = strLine.Split(',');


                if (tableHead.Length > columnCount)
                {
                    for (int i = columnCount; i < tableHead.Length; i++)
                    {
                        DataColumn dc = new DataColumn(tableHead[i]);
                        dt.Columns.Add(dc);
                    }
                    columnCount = tableHead.Length;
                    //tableHead = stableHead;


                }
                aryLine = strLine.Split(',');
                DataRow dr = dt.NewRow();
                for (int j = 0; j < aryLine.Length; j++)
                {
                    dr[j] = aryLine[j];
                }
                dt.Rows.Add(dr);


            }

            sr.Close();
            fs.Close();
            return dt;
        }

        public static Encoding GetType(string FILE_NAME)
        {
            FileStream fs = new FileStream(FILE_NAME, FileMode.Open,
              FileAccess.Read);
            Encoding r = GetType(fs);
            fs.Close();
            return r;
        }
        /// 通过给定的文件流，判断文件的编码类型
        /// <param name="fs">文件流</param>
        /// <returns>文件的编码类型</returns>
        public static Encoding GetType(FileStream fs)
        {
            byte[] Unicode = new byte[] { 0xFF, 0xFE, 0x41 };
            byte[] UnicodeBIG = new byte[] { 0xFE, 0xFF, 0x00 };
            byte[] UTF8 = new byte[] { 0xEF, 0xBB, 0xBF }; //带BOM
            Encoding reVal = Encoding.Default;

            BinaryReader r = new BinaryReader(fs, Encoding.Default);
            int i;
            int.TryParse(fs.Length.ToString(), out i);
            byte[] ss = r.ReadBytes(i);
            if (IsUTF8Bytes(ss) || (ss[0] == 0xEF && ss[1] == 0xBB && ss[2] == 0xBF))
            {
                reVal = Encoding.UTF8;
            }
            else if (ss[0] == 0xFE && ss[1] == 0xFF && ss[2] == 0x00)
            {
                reVal = Encoding.BigEndianUnicode;
            }
            else if (ss[0] == 0xFF && ss[1] == 0xFE && ss[2] == 0x41)
            {
                reVal = Encoding.Unicode;
            }
            r.Close();
            return reVal;
        }

        /// 判断是否是不带 BOM 的 UTF8 格式
        /// <param name="data"></param>
        /// <returns></returns>
        private static bool IsUTF8Bytes(byte[] data)
        {
            int charByteCounter = 1;　 //计算当前正分析的字符应还有的字节数
            byte curByte; //当前分析的字节.
            for (int i = 0; i < data.Length; i++)
            {
                curByte = data[i];
                if (charByteCounter == 1)
                {
                    if (curByte >= 0x80)
                    {
                        //判断当前
                        while (((curByte <<= 1) & 0x80) != 0)
                        {
                            charByteCounter++;
                        }
                        //标记位首位若为非0 则至少以2个1开始 如:110XXXXX...........1111110X　
                        if (charByteCounter == 1 || charByteCounter > 6)
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    //若是UTF-8 此时第一位必须为1
                    if ((curByte & 0xC0) != 0x80)
                    {
                        return false;
                    }
                    charByteCounter--;
                }
            }
            if (charByteCounter > 1)
            {
                throw new Exception("非预期的byte格式");
            }
            return true;
        }

        /// <summary>
        /// DateTable转list泛型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> TableToListModel<T>(DataTable dt) where T : new()
        {
            // 定义集合    
            List<T> ts = new List<T>();

            // 获得此模型的类型   
            Type type = typeof(T);
            string tempName = "";

            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                // 获得此模型的公共属性      
                PropertyInfo[] propertys = t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;  // 检查DataTable是否包含此列    

                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter      
                        if (!pi.CanWrite) continue;

                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }

        /// <summary>
        /// 对象 List<>转DataTable
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static DataTable ObjectToTable(object obj)
        {
            try
            {
                Type t;
                if (obj.GetType().IsGenericType)
                {
                    t = obj.GetType().GetGenericTypeDefinition();
                }
                else
                {
                    t = obj.GetType();
                }
                if (t == typeof(List<>) ||
                    t == typeof(IEnumerable<>))
                {
                    DataTable dt = new DataTable();
                    IEnumerable<object> lstenum = obj as IEnumerable<object>;
                    if (lstenum.Count() > 0)
                    {
                        var ob1 = lstenum.GetEnumerator();
                        ob1.MoveNext();
                        foreach (var item in ob1.Current.GetType().GetProperties())
                        {
                            dt.Columns.Add(new DataColumn() { ColumnName = item.Name });
                        }
                        //数据
                        foreach (var item in lstenum)
                        {
                            DataRow row = dt.NewRow();
                            foreach (var sub in item.GetType().GetProperties())
                            {
                                row[sub.Name] = sub.GetValue(item, null);
                            }
                            dt.Rows.Add(row);
                        }
                        return dt;
                    }
                }
                else if (t == typeof(DataTable))
                {
                    return (DataTable)obj;
                }
                else   //(t==typeof(Object))
                {
                    DataTable dt = new DataTable();
                    foreach (var item in obj.GetType().GetProperties())
                    {
                        dt.Columns.Add(new DataColumn() { ColumnName = item.Name });
                    }
                    DataRow row = dt.NewRow();
                    foreach (var item in obj.GetType().GetProperties())
                    {
                        row[item.Name] = item.GetValue(obj, null);
                    }
                    dt.Rows.Add(row);
                    return dt;
                }

            }
            catch
            {

            }
            return null;
        }

        internal static string ToDBC(object p)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 内存对象转换为json字符串
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string ToJSON(object obj)
        {
            StringBuilder sb = new StringBuilder();
            JavaScriptSerializer json = new JavaScriptSerializer();
            json.Serialize(obj, sb);
            return sb.ToString();
        }
        /// <summary>
        /// Json字符串转内存对象
        /// </summary>
        /// <param name="jsonString"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static T FromJSON<T>(string jsonString)
        {
            JavaScriptSerializer json = new JavaScriptSerializer();
            return json.Deserialize<T>(jsonString);
        }
    }
}
